﻿#region Đào Mạnh Hòa
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Transactions;

namespace BusinessLogic
{

    public static class TraLaiNCC
    {

        static SqlCommand cmd;
        static ArrayList arr;
        static SqlDataReader dr;
        static SqlConnection cn;

        #region Select Trả Lại Nhà Cung Cấp
        public static Entities.TraLaiNCC[] Select()
        {
            Entities.TraLaiNCC[] list = null;
            try
            {
                string sql = Common.Constants.TraLaiNCC.Select;
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                arr = new ArrayList();
                while (dr.Read())
                {
                    Entities.TraLaiNCC TraLaiNCC = new Entities.TraLaiNCC();
                    TraLaiNCC.TraLaiNCCID = int.Parse(dr[Common.Constants.TraLaiNCC.TraLaiNCCID].ToString());
                    TraLaiNCC.MaHDTraLaiNCC = dr[Common.Constants.TraLaiNCC.MaHDTraLaiNCC].ToString();
                    TraLaiNCC.MaNhanVien = (dr[Common.Constants.TraLaiNCC.MaNhanVien].ToString());
                    TraLaiNCC.Ngaytra = DateTime.Parse(dr[Common.Constants.TraLaiNCC.Ngaytra].ToString());
                    TraLaiNCC.MaNCC = dr[Common.Constants.TraLaiNCC.MaNCC].ToString();
                    TraLaiNCC.TenNCC = dr[Common.Constants.TraLaiNCC.TenNCC].ToString();
                    TraLaiNCC.NoHienThoi = Common.Utilities.FormatMoney(dr[Common.Constants.TraLaiNCC.NoHienThoi].ToString());
                    TraLaiNCC.NguoiNhanHang = dr[Common.Constants.TraLaiNCC.NguoiNhanHang].ToString();
                    TraLaiNCC.MaKho = dr[Common.Constants.TraLaiNCC.MaKho].ToString();
                    TraLaiNCC.TenKho = dr[Common.Constants.TraLaiNCC.TenKho].ToString();
                    TraLaiNCC.TienBoiThuong = dr[Common.Constants.TraLaiNCC.TienBoiThuong].ToString();
                    TraLaiNCC.ThanhToanNgay = dr[Common.Constants.TraLaiNCC.ThanhToanNgay].ToString();
                    TraLaiNCC.ThanhToanSauKhiLapPhieu = dr[Common.Constants.TraLaiNCC.ThanhToanSauKhiLapPhieu].ToString();
                    TraLaiNCC.Deleted = bool.Parse(dr[Common.Constants.TraLaiNCC.Deleted].ToString());
                    TraLaiNCC.GhiChu = dr[Common.Constants.TraLaiNCC.GhiChu].ToString();
                    arr.Add(TraLaiNCC);
                }
                return (Entities.TraLaiNCC[])arr.ToArray(typeof(Entities.TraLaiNCC));
            }
            catch (Exception ex)
            { string s = ex.Message.ToString(); return new Entities.TraLaiNCC[0]; }
            finally
            {
                cmd.Connection.Dispose();
                cn.Close();
            }

        }
        #endregion

        #region Insert Update Trả Lại Nhà Cung Cấp
        // insert update
        public static int InsertUpdate(Entities.TraLaiNCC TraLaiNCC)
        {
            TransactionScope ts = null;
            int rt = 0;
            try
            {
                using (ts = new TransactionScope(TransactionScopeOption.Required))
                {
                    string sql = Common.Constants.TraLaiNCC.InsertUpdate;
                    SqlConnection cn = DataAccess.Connection.OpenConnection();
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.HanhDong, SqlDbType.NVarChar, 50).Value = TraLaiNCC.HanhDong;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.TraLaiNCCID, SqlDbType.Int).Value = TraLaiNCC.TraLaiNCCID;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.MaHDTraLaiNCC, SqlDbType.VarChar, 50).Value = TraLaiNCC.MaHDTraLaiNCC;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.ThueGTGT, SqlDbType.Float).Value = TraLaiNCC.ThueGTGT;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.MaNhanVien, SqlDbType.VarChar, 50).Value = TraLaiNCC.MaNhanVien;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.Ngaytra, SqlDbType.DateTime).Value = TraLaiNCC.Ngaytra;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.MaNCC, SqlDbType.VarChar, 50).Value = TraLaiNCC.MaNCC;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.TenNCC, SqlDbType.NVarChar, 200).Value = TraLaiNCC.TenNCC;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.NoHienThoi, SqlDbType.Float).Value = TraLaiNCC.NoHienThoi;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.NguoiNhanHang, SqlDbType.NVarChar, 200).Value = TraLaiNCC.NguoiNhanHang;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.MaKho, SqlDbType.VarChar, 50).Value = TraLaiNCC.MaKho;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.TenKho, SqlDbType.NVarChar, 200).Value = TraLaiNCC.TenKho;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.TienBoiThuong, SqlDbType.Float).Value = TraLaiNCC.TienBoiThuong;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.ThanhToanNgay, SqlDbType.Float).Value = TraLaiNCC.ThanhToanNgay;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.ThanhToanSauKhiLapPhieu, SqlDbType.Float).Value = TraLaiNCC.ThanhToanSauKhiLapPhieu;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.GhiChu, SqlDbType.NVarChar, 200).Value = TraLaiNCC.GhiChu;
                    cmd.Parameters.Add(Common.Constants.TraLaiNCC.Deleted, SqlDbType.Bit).Value = TraLaiNCC.Deleted;
                    rt = cmd.ExecuteNonQuery();
                    // check insert chi tiết trả lại nhà cung cấp
                    if (ChiTietTraLaiNCC.InsertUpdate(TraLaiNCC.ChiTietTraLaiNCC) == 0)
                    {
                        ts.Dispose();
                        return 0;
                    }
                    // check trừ số lương chi tiết kho hàng
                    if (TruSoLuong(TraLaiNCC.ChiTietTraLaiNCC) == 0)
                    {
                        ts.Dispose();
                        return 0;
                    }
                    // check update chi tiết hóa đơn nhập
                    if (InsertUpdate(TraLaiNCC.ChiTietHoaDonNhap) == 0)
                    {
                        ts.Dispose();
                        return 0;
                    }
                    // check update dư nợ nhà cung cấp
                    Entities.ChiTietCongNoNCC temp = new Entities.ChiTietCongNoNCC();
                    temp.HanhDong = "1";
                    temp.MaKho = TraLaiNCC.MaKho;
                    temp.MaNhaCungCap = TraLaiNCC.MaNCC;
                    temp.DuNo = (double.Parse(TraLaiNCC.ThanhToanNgay) - double.Parse(TraLaiNCC.TienBoiThuong)).ToString();
                    Entities.ChiTietCongNoNCC[] chitiet = ChiTietCongNoNCC.Select(temp);
                    if (chitiet != null && chitiet.Length != 0)
                        temp.HanhDong = "Update";
                    else
                        temp.HanhDong = "Insert";
                    if (ChiTietCongNoNCC.InsertUpdate(temp) == 0)
                    {
                        ts.Dispose();
                        return 0;
                    }
                    // check update thanh toán ngay hóa đơn nhập
                    if (double.Parse(TraLaiNCC.ThanhToanNgay) != 0)
                    {
                        // Check insert phiếu thanh toán nhà cung cấp
                        if (InsertUpdate(TraLaiNCC.PhieuTTNCC) == 0)
                        {
                            ts.Dispose();
                            return 0;
                        }
                        // check insert chi tiết phiếu thanh toán nhà cung cấp
                        if (InsertUpdate(TraLaiNCC.ChiTietPhieuTTNCC)==0)
                        {
                            ts.Dispose();
                            return 0;
                        }
                        // check update thanh toán ngay sau khi lập phiếu hóa đơn nhập
                        if (InsertUpdate(TraLaiNCC.ChiTietHoaDonNhap) == 0)
                        {
                            ts.Dispose();
                            return 0;
                        }
                    }
                    LuuVietHeThong.LuuViet(
                        "Thêm hóa đơn",
                        TraLaiNCC.TaiKhoan.MaNhanVien,
                        "Thêm hóa đơn mới " + TraLaiNCC.MaHDTraLaiNCC,
                        TraLaiNCC.TaiKhoan.TenDangNhap,
                        DateTime.Now.ToString("dd/MM/YYYY"));
                    ts.Complete();
                    return rt;
                }
            }
            catch
            {
                ts.Dispose();
                return 0;
            }

        }

        #region insert update phiếu thanh toán nhà cung cấp
        public static int InsertUpdate(Entities.PhieuTTNCC phieuthanhtoannhacungcap)
        {
            int i = 0;
            try
            {
                string sql = Common.Constants.PhieuTTNCC.InsertUpdatePhieuTTNCC;
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);

                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.HanhDong, SqlDbType.NVarChar, 20).Value = phieuthanhtoannhacungcap.HanhDong;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.PhieuTTNCCID, SqlDbType.Int).Value = phieuthanhtoannhacungcap.PhieuTTNCCID;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.MaPhieuTTNCC, SqlDbType.VarChar, 50).Value = phieuthanhtoannhacungcap.MaPhieuTTNCC;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.NgayLap, SqlDbType.DateTime).Value = phieuthanhtoannhacungcap.NgayLap;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.MaNhaCungCap, SqlDbType.VarChar, 50).Value = phieuthanhtoannhacungcap.MaNhaCungCap;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.TenNhaCungCap, SqlDbType.NVarChar, 200).Value = phieuthanhtoannhacungcap.TenNhaCungCap;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.TongThanhToan, SqlDbType.Float).Value = phieuthanhtoannhacungcap.TongThanhToan;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.NoHienThoi, SqlDbType.Float).Value = phieuthanhtoannhacungcap.NoHienThoi;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.Nguoinhan, SqlDbType.NVarChar, 200).Value = phieuthanhtoannhacungcap.Nguoinhan;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.MaNhanVien, SqlDbType.VarChar, 50).Value = phieuthanhtoannhacungcap.MaNhanVien;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.MaKho, SqlDbType.VarChar, 50).Value = phieuthanhtoannhacungcap.MaKho;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.LoaiPhieu, SqlDbType.Bit).Value = phieuthanhtoannhacungcap.LoaiPhieu;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.GhiChu, SqlDbType.NVarChar, 200).Value = phieuthanhtoannhacungcap.GhiChu;
                cmd.Parameters.Add(Common.Constants.PhieuTTNCC.Deleted, SqlDbType.Bit).Value = phieuthanhtoannhacungcap.Deleted;
                i = cmd.ExecuteNonQuery();
                cmd.Connection.Dispose();
                cn.Close();
                cn = null;
                return i;
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        #region Insert Update Chi Tiết Phiếu Thanh Toán Nhà Cung Cấp
        public static int InsertUpdate(Entities.ChiTietPhieuTTNCC chitietphieuttncc)
        {
            try
            {
                string sql = Common.Constants.ChiTietPhieuTTNCC.InsertUpdate;
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);

                cmd.Parameters.Add(Common.Constants.ChiTietPhieuTTNCC.HanhDong, SqlDbType.NVarChar, 50).Value = chitietphieuttncc.HanhDong;
                cmd.Parameters.Add(Common.Constants.ChiTietPhieuTTNCC.MaHoaDon, SqlDbType.VarChar, 50).Value = chitietphieuttncc.MaHoaDonNhap;
                cmd.Parameters.Add(Common.Constants.ChiTietPhieuTTNCC.MaPhieuTTNCC, SqlDbType.VarChar, 50).Value = chitietphieuttncc.MaPhieuTTNCC;
                cmd.Parameters.Add(Common.Constants.ChiTietPhieuTTNCC.TongTien, SqlDbType.Float).Value = chitietphieuttncc.TongTienThanhToan;
                cmd.Parameters.Add(Common.Constants.ChiTietPhieuTTNCC.TienNo, SqlDbType.Float).Value = chitietphieuttncc.SoNo;
                cmd.Parameters.Add(Common.Constants.ChiTietPhieuTTNCC.ThanhToan, SqlDbType.Float).Value = chitietphieuttncc.ThanhToan;
                cmd.Parameters.Add(Common.Constants.ChiTietPhieuTTNCC.GhiChu, SqlDbType.NVarChar, 200).Value = "";
                cmd.Parameters.Add(Common.Constants.ChiTietPhieuTTNCC.Deleted, SqlDbType.Bit).Value = false;

                int i = cmd.ExecuteNonQuery();
                cmd.Connection.Dispose();
                cn.Close();
                cn = null;
                return i;
            }
            catch
            {
                return 0;
            }

        }

        public static int InsertUpdate(Entities.ChiTietPhieuTTNCC[] chitietphieuttncc)
        {
            try
            {
                for (int i = 0; i < chitietphieuttncc.Length; i++)
                {
                    if (InsertUpdate(chitietphieuttncc[i]) == 0)
                    {
                        return 0;

                    }
                }
                return 1;
            }
            catch
            {
                return 0;

            }

        }
        #endregion

        #region Insert Update Chi Tiết Hóa Đơn Nhập
        // insert update chi tiết hóa đơn nhập
        public static int InsertUpdate(Entities.ChiTietHoaDonNhap TraLaiNCC)
        {
            int rt = 0;
            try
            {
                string sql = Common.Constants.TraLaiNCC.UpdateHDN;
                SqlConnection cn = DataAccess.Connection.OpenConnection();
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.Parameters.Add(Common.Constants.ChiTietHoaDonNhap.MaHoaDonNhap, SqlDbType.VarChar, 50).Value = TraLaiNCC.MaHoaDonNhap;
                cmd.Parameters.Add(Common.Constants.ChiTietHoaDonNhap.MaHangHoa, SqlDbType.VarChar, 50).Value = TraLaiNCC.MaHangHoa;
                cmd.Parameters.Add("SoLuongConLai", SqlDbType.Float).Value = TraLaiNCC.SoLuongConLai;
                rt = cmd.ExecuteNonQuery();
                return rt;
            }
            catch (Exception ex)
            { string s = ex.Message.ToString(); return 0; }

        }

        public static int InsertUpdate(Entities.ChiTietHoaDonNhap[] chitietkhohang)
        {
            try
            {
                foreach (Entities.ChiTietHoaDonNhap item in chitietkhohang)
                {
                    if (InsertUpdate(item) == 0)
                    {
                        return 0;
                    }
                }
                return 1;
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        #region Update Dư Nợ Nhà Cung Cấp
        // update dư nợ nhà cung cấp
        public static int InsertUpdate(string manhacungcap, string duno)
        {
            int rt = 0;
            try
            {
                string sql = Common.Constants.TraLaiNCC.UpdateDuNoNCC;
                SqlConnection cn = DataAccess.Connection.OpenConnection();
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.Parameters.Add(Common.Constants.NhaCungCap.MaNhaCungCap, SqlDbType.VarChar, 50).Value = manhacungcap;
                cmd.Parameters.Add(Common.Constants.NhaCungCap.NoHienThoi, SqlDbType.Float).Value = duno;
                rt = cmd.ExecuteNonQuery();
                return rt;
            }
            catch (Exception ex)
            { string s = ex.Message.ToString(); return 0; }

        }
        #endregion

        #region Trừ Số Lượng
        /// <summary>
        ///  trừ số lượng
        /// </summary>
        /// <param name="chititetkhohang"></param>
        /// <returns></returns>
        public static int TruSoLuong(Entities.ChiTietTraLaiNCC chititetkhohang)
        {
            try
            {
                string sql = Common.Constants.TraLaiNCC.TruSoLuong;
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(sql, cn);

                cmd.Parameters.Add(Common.Constants.ChiTietKhoHang.MaKho, SqlDbType.VarChar, 50).Value = chititetkhohang.MaKho;
                cmd.Parameters.Add(Common.Constants.ChiTietKhoHang.MaHangHoa, SqlDbType.VarChar, 50).Value = chititetkhohang.MaHangHoa;
                cmd.Parameters.Add(Common.Constants.ChiTietKhoHang.SoLuong, SqlDbType.Float).Value = chititetkhohang.SoLuong;

                int i = cmd.ExecuteNonQuery();

                cmd.Connection.Dispose();
                cn.Close();
                cn = null;
                return i;
            }
            catch (Exception ex)
            {
                string s = ex.Message;
                return 0;
            }
        }

        public static int TruSoLuong(Entities.ChiTietTraLaiNCC[] chitietkhohang)
        {
            try
            {
                foreach (Entities.ChiTietTraLaiNCC item in chitietkhohang)
                {
                    if (TruSoLuong(item) == 0)
                    {
                        return 0;
                    }
                }
                return 1;
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        #endregion

        /// <summary>
        /// loivt
        /// </summary>
        /// <param name="nhap"></param>
        /// <returns></returns>
        public static Entities.TraLaiNCC[] SelectHDTraLaiNCCTheoMaNCC(string mancc)
        {
            Entities.TraLaiNCC[] list = null;
            try
            {
                cn = DataAccess.Connection.OpenConnection();
                cmd = new SqlCommand(Common.Constants.TraLaiNCC.SelectHDTraLaiNCCTheoMaNCC, cn);
                cmd.Parameters.Add(Common.Constants.TraLaiNCC.MaNCC, SqlDbType.VarChar).Value = mancc;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                arr = new ArrayList();
                while (dr.Read())
                {
                    Entities.TraLaiNCC TraLaiNCC = new Entities.TraLaiNCC();
                    TraLaiNCC.TraLaiNCCID = int.Parse(dr[Common.Constants.TraLaiNCC.TraLaiNCCID].ToString());
                    TraLaiNCC.MaHDTraLaiNCC = dr[Common.Constants.TraLaiNCC.MaHDTraLaiNCC].ToString();
                    TraLaiNCC.MaNhanVien = (dr[Common.Constants.TraLaiNCC.MaNhanVien].ToString());
                    TraLaiNCC.Ngaytra = DateTime.Parse(dr[Common.Constants.TraLaiNCC.Ngaytra].ToString());
                    TraLaiNCC.MaNCC = dr[Common.Constants.TraLaiNCC.MaNCC].ToString();
                    TraLaiNCC.TenNCC = dr[Common.Constants.TraLaiNCC.TenNCC].ToString();
                    TraLaiNCC.NoHienThoi = Common.Utilities.FormatMoney(dr[Common.Constants.TraLaiNCC.NoHienThoi].ToString());
                    TraLaiNCC.NguoiNhanHang = dr[Common.Constants.TraLaiNCC.NguoiNhanHang].ToString();
                    TraLaiNCC.MaKho = dr[Common.Constants.TraLaiNCC.MaKho].ToString();
                    TraLaiNCC.TenKho = dr[Common.Constants.TraLaiNCC.TenKho].ToString();
                    TraLaiNCC.TienBoiThuong = dr[Common.Constants.TraLaiNCC.TienBoiThuong].ToString();
                    TraLaiNCC.ThanhToanNgay = dr[Common.Constants.TraLaiNCC.ThanhToanNgay].ToString();
                    TraLaiNCC.ThanhToanSauKhiLapPhieu = dr[Common.Constants.TraLaiNCC.ThanhToanSauKhiLapPhieu].ToString();
                    TraLaiNCC.Deleted = bool.Parse(dr[Common.Constants.TraLaiNCC.Deleted].ToString());
                    TraLaiNCC.GhiChu = dr[Common.Constants.TraLaiNCC.GhiChu].ToString();
                    arr.Add(TraLaiNCC);
                }
                int n = arr.Count;
                if (n == 0) { list = null; }
                list = new Entities.TraLaiNCC[n];
                for (int i = 0; i < n; i++)
                {
                    list[i] = (Entities.TraLaiNCC)arr[i];
                }
            }
            catch (Exception ex)
            { string s = ex.Message.ToString(); list = null; }
            finally
            {
                cmd.Connection.Dispose();
                cn.Close();
            }
            return list;
        }

    }
}
#endregion